# !/bin/bash

if [ -n "$1" ]; then
    data_date=$1
else
  data_date=$(date -d "-1 days" +%F)
fi

dim_md_areas="
with t_area as (
    select
        ID,AREANAME,PARENTID,BD09_LNG,BD09_LAT
    from jtp_app_ods.ods_md_areas
    where dt='${data_date}'
),
    t1 as (
        select
            ID,AREANAME as province_name
        from t_area
        where PARENTID=0
    ),t2 as (
        select
            t_area.ID,
            t1.province_name,
            t_area.AREANAME as city_name
            from t1 left join  t_area on t_area.PARENTID=t1.ID
),t3 as (
    select
        t_area.ID,
        t2.province_name,
        t2.city_name,
        t_area.AREANAME as street_name
        from t2 left join  t_area on t_area.PARENTID=t2.ID
)
insert overwrite table jtp_app_dim.dim_md_areas partition (dt='${data_date}')
select
    t_area.ID,
    t3.province_name,
    t3.city_name,
    t3.street_name,
    t_area.AREANAME as village_name,
    t_area.BD09_LAT,
    t_area.BD09_LNG,
    cottt(BD09_LAT,BD09_LNG,6) AS gep_hash
from t3 left join t_area on t3.ID=t_area.PARENTID
where t_area.BD09_LAT is not null and t_area.BD09_LNG is not null ;"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${dim_md_areas}"